package com.ccl.iot.ir;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Environment;
import android.util.Log;

import com.alibaba.fastjson.JSON;
import com.ccl.iot.IOTDataType;
import com.ccl.iot.bean.CodeviewBean;
import com.ccl.iot.bean.IrToBatchItemBean;
import com.ccl.iot.db.SqlOpenHelper;
import com.ccl.iot.device.IOTBLDNADevice;
import com.ccl.iot.managers.AuthManager;
import com.ccl.iot.managers.Manager;
import com.kookong.app.data.IrData;
import com.mysql.jdbc.CallableStatement;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.Statement;

import java.io.File;
import java.io.FileOutputStream;
import java.math.BigDecimal;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;

import cn.com.broadlink.sdk.data.controller.BLDNADevice;

/**
 * 创建者     王浩
 * 创建时间   2017/5/12 14:47
 * 描述	      ${TODO}
 */

public class DBUtils {

    static {
        try {
            // 加载数据库驱动程序
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            Log.e("JDBC", "加载驱动错误: " + e.getMessage());
        }
    }

    private static String sql = " (idx int, DevID int, SubDevID int, DevType text, KeyType text, KeyName text, CodeType text, aCode text, CodeID int, version int)";

    private static SQLiteDatabase mIrCodeDb = null;

    public static final String CODE_TYPE_API = "API";
    public static final String CODE_TYPE_FIXED = "Fixed";
    public static final String CODE_TYPE_STATUS = "Status";

    /**
     * 获取数据库连接
     *
     * @return
     */
    public static Connection getDBConn() {
        String url = "jdbc:mysql://cclcp.com:50000/iotirserver";
        String username = "ccliot";
        String password = "CCLIOTDBPassword2017051108";
        Connection conn = null;
        try {
            conn = (Connection) DriverManager.getConnection(url, username, password);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }

    /**
     * 根据用户id获取用户的所有红外设备
     *
     * @param user
     * @param dbConn
     * @return
     */
    public static ArrayList<IOTBLDNADevice> getAllIRDevice(long user, Connection dbConn) {
        ArrayList<IOTBLDNADevice> irDevs = null;
        try {
            Statement statement = (Statement) dbConn.createStatement();

            //根据用户id查询
            ResultSet resultSet = statement.executeQuery("select * from udmaps where User=" + IOTDataType.CU64(user).toString());
            Log.e("DBIR", "User: " + user);
            irDevs = new ArrayList<IOTBLDNADevice>();
            while (resultSet.next()) {
                long id = resultSet.getLong("DevID");
                Log.e("DBIR", "DevID: " + id);
                IOTBLDNADevice irDev = getIRDev(id, dbConn);
                if (irDev != null && irDev.getType() == 10039) {
                    irDevs.add(irDev);
                }
            }

            resultSet.close();
            statement.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return irDevs;
    }

    /**
     * 根据设备id获取设备的基本字段构建设备
     *
     * @param id
     * @param dbConn
     * @return
     */
    public static IOTBLDNADevice getIRDev(long id, Connection dbConn) {
        IOTBLDNADevice dev = null;
        try {
            Statement statement = (Statement) dbConn.createStatement();
            ResultSet idResult = statement.executeQuery("select * from devinfos where ID=" + id);
            if (idResult.next()) {
                dev = new IOTBLDNADevice();
                dev.setDbId(id);
                dev.setDid(idResult.getString("did"));
                dev.setPid(idResult.getString("pid"));
                dev.setId(idResult.getInt("controlId"));
                dev.setKey(idResult.getString("controlKey"));
                dev.setMac(idResult.getString("mac"));
                dev.setType(idResult.getInt("type"));
            }
            idResult.close();
            statement.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return dev;
    }

    /**
     * 根据单个设备id, 获取这个设备的红外码
     *
     * @param id
     * @param dbConn
     * @return
     */
    public static ArrayList<CodeviewBean> getAllCodeview(long id, Connection dbConn) {
        ArrayList<CodeviewBean> codeBeans = null;
        if (mIrCodeDb != null && mIrCodeDb.isOpen()) {
            mIrCodeDb.close();
        }
        mIrCodeDb = new SqlOpenHelper(Manager.DBCONTEXT, AuthManager.DB_NAME, "ircodes", sql).getWritableDatabase();
        Cursor verCursor = mIrCodeDb.query("ircodes", new String[]{"version"}, "idx=? and DevID=?", new String[]{"0", "" + id}, null, null, null);
        long version = -1;
        if (verCursor != null && verCursor.moveToNext()) {
            version = verCursor.getLong(0);
            verCursor.close();
        }

        long subDevVer = getSubDevVer(id, dbConn);
        if (version == subDevVer) {
            //            (index integer, DevID integer, SubDevID integer, DevType text, KeyType text, , CodeType text,, aCode text, CodeID integer, version integer)";
            Cursor ircodes = mIrCodeDb.query("ircodes", new String[]{"SubDevID, DevType, KeyType, KeyName, CodeType, aCode, CodeID"}, "DevID=?", new String[]{"" + id}, null, null, null);
            if (ircodes != null) {
                codeBeans = new ArrayList<CodeviewBean>();
                while (ircodes.moveToNext()) {
                    if (DBUtils.CODE_TYPE_API.equals(ircodes.getString(2))) {
                        String code = ircodes.getString(5);
                        IrData irData = JSON.parseObject(code, IrData.class);
                        if (irData != null) {
                            CodeviewBean codeviewBean = new CodeviewBean(id,
                                    ircodes.getInt(0), ircodes.getString(1), irData, DBUtils.CODE_TYPE_API, ircodes.getLong(6));
                            codeBeans.add(codeviewBean);
                        }
                    } else {
                        CodeviewBean codeviewBean = new CodeviewBean(id,
                                ircodes.getInt(0), ircodes.getString(1), ircodes.getString(2),
                                ircodes.getString(3), ircodes.getString(4), ircodes.getString(5), ircodes.getLong(6));
                        codeBeans.add(codeviewBean);
                    }
                }
                ircodes.close();
            }
        } else {
            mIrCodeDb.delete("ircodes", "DevID=?", new String[]{id + ""});

            if (dbConn != null) {
                try {
                    Statement statement = (Statement) dbConn.createStatement();
                    ResultSet resultSet = statement.executeQuery("select SubDevID,DevType,KeyType,KeyName,CodeID,Type,HEX(Code) AS Code from codeview where DevID=" + id);
                    codeBeans = new ArrayList<CodeviewBean>();
                    ContentValues cValue = new ContentValues();
                    int index = 0;
                    while (resultSet.next()) {
                        //                    CodeviewBean codeviewBean = new CodeviewBean(resultSet.getLong("DevID"),
                        CodeviewBean codeviewBean = null;
                        if (DBUtils.CODE_TYPE_API.equals(resultSet.getString("KeyType"))) {
                            Statement acStatement = (Statement) dbConn.createStatement();
                            ResultSet acResultSet = acStatement.executeQuery("select Code from codes where ID=" + resultSet.getLong("CodeID"));
                            if (acResultSet.next()) {
                                int apiid = acResultSet.getInt("Code");
                                String code = getAcCode(apiid, dbConn);
                                try {
                                    FileOutputStream fos = new FileOutputStream(new File(Environment.getExternalStorageDirectory().getPath() + "/jjjjjsonnn" + resultSet.getInt("SubDevID") + ".txt"));
                                    fos.write(code.getBytes());
                                    fos.flush();
                                    fos.close();
                                } catch (Exception e) {
                                    e.printStackTrace();
                                }
                                IrData irData = JSON.parseObject(code, IrData.class);
                                if (irData != null) {
                                    codeviewBean = new CodeviewBean(id,
                                            resultSet.getInt("SubDevID"), resultSet.getString("DevType"), irData, DBUtils.CODE_TYPE_API, resultSet.getLong("CodeID"));
                                    codeviewBean.aCode = code;
                                }
                            }
                            acResultSet.close();
                        } else {
                            codeviewBean = new CodeviewBean(id,
                                    resultSet.getInt("SubDevID"), resultSet.getString("DevType"), resultSet.getString("KeyType"),
                                    resultSet.getString("KeyName"),
                                    resultSet.getString("Type"), resultSet.getString("Code"), resultSet.getLong("CodeID"));
                        }
                        if (codeviewBean != null) {
                            codeBeans.add(codeviewBean);
                            cValue.clear();
                            cValue.put("idx", index);
                            cValue.put("DevID", id);
                            cValue.put("SubDevID", codeviewBean.SubDevID);
                            cValue.put("DevType", codeviewBean.DevType);
                            cValue.put("KeyType", codeviewBean.KeyType);
                            cValue.put("KeyName", codeviewBean.KeyName);
                            cValue.put("CodeType", codeviewBean.CodeType);
                            cValue.put("aCode", codeviewBean.aCode);
                            cValue.put("CodeID", codeviewBean.CodeID);
                            cValue.put("version", subDevVer);
                            mIrCodeDb.insert("ircodes", null, cValue);
                            index++;
                        }
                    }
                    resultSet.close();
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        mIrCodeDb.close();
        return codeBeans;
    }

    private static String getAcCode(int id, Connection dbConn) {
        if (dbConn != null) {

            try {
                Statement statement = (Statement) dbConn.createStatement();
                ResultSet resultSet = statement.executeQuery("select Code from apicodes where ID=" + id);
                if (resultSet.next()) {
                    String code = resultSet.getString("Code");
                    statement.close();
                    return code;
                }
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    /**
     * 获取SubDev版本号
     *
     * @param dbId
     * @param dbConn
     * @return
     */
    public static long getSubDevVer(long dbId, Connection dbConn) {
        if (dbConn != null) {

            try {
                Statement statement = (Statement) dbConn.createStatement();
                ResultSet resultSet = statement.executeQuery("select Version from vers where DevID=" + dbId + " and SubDevID=" + 0);
                if (resultSet.next()) {
                    long version = resultSet.getLong("Version");
                    statement.close();
                    return version;
                }
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return 0;
    }

    /**
     * 添加一个红外设备到服务器
     *
     * @param dev
     * @param dbConn
     * @return
     */
    public static IOTBLDNADevice addIRDev(BLDNADevice dev, Connection dbConn) {
        //        IOTBLDNADevice irDev = getIRDev(dev.getDid(), dbConn);
        IOTBLDNADevice irDev = null;
        //        if (irDev == null) {
        CallableStatement cstmt = null;
        try {

            String sql = "{? = call AddDevice(?,?,?,?,?,?)}";
            cstmt = (CallableStatement) dbConn.prepareCall(sql);
            cstmt.registerOutParameter(1, Types.BIGINT);
            cstmt.setString(2, dev.getDid());
            cstmt.setString(3, dev.getMac());
            cstmt.setString(4, dev.getPid());
            cstmt.setInt(5, dev.getType());
            cstmt.setInt(6, dev.getId());
            cstmt.setString(7, dev.getKey());
            boolean execute = cstmt.execute();
            long result = cstmt.getLong(1);
            Log.e("onDeviceUpdate", "execute: " + execute + ", result: " + result);
            cstmt.close();
            if (result != 0) {
                irDev = new IOTBLDNADevice();
                irDev.setDid(dev.getDid());
                irDev.setPid(dev.getPid());
                irDev.setId(dev.getId());
                irDev.setKey(dev.getKey());
                irDev.setMac(dev.getMac());
                irDev.setType(dev.getType());
                irDev.setDbId(result);
                return irDev;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        //        }
        return irDev;
    }

    /**
     * 将设备设用户绑定
     *
     * @param dbId
     * @param user
     * @param dbConn
     * @return
     */
    public static String bindUserDev(long dbId, long user, Connection dbConn) {
        String sql = "{? = call BindDevice(?,?)}";
        CallableStatement cstmt = null;
        try {
            cstmt = (CallableStatement) dbConn.prepareCall(sql);
            cstmt.registerOutParameter(1, Types.VARCHAR);
            cstmt.setBigDecimal(2, new BigDecimal(IOTDataType.CU64(user)));
            cstmt.setLong(3, dbId);
            cstmt.execute();
            String result = cstmt.getString(1);
            cstmt.close();
            Log.e("bindUserDev", "bindUserDev result: " + result);
            cstmt.close();
            return result;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 将设备设用户解绑
     *
     * @param dbId
     * @param user
     * @return
     */
    public static String unbindUserDev(long dbId, long user) {
        Connection dbConn = getDBConn();
        if (dbConn != null) {
            String sql = "{? = call UnbindDevice(?,?)}";
            CallableStatement cstmt = null;
            try {
                cstmt = (CallableStatement) dbConn.prepareCall(sql);
                cstmt.registerOutParameter(1, Types.VARCHAR);
                cstmt.setBigDecimal(2, new BigDecimal(IOTDataType.CU64(user)));
                cstmt.setLong(3, dbId);
                cstmt.execute();
                String result = cstmt.getString(1);
                cstmt.close();
                Log.e("bindUserDev", "UnbindDevice result: " + result);
                cstmt.close();
                return result;
            } catch (SQLException e) {
                try {
                    dbConn.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
                e.printStackTrace();
            }
        }
        return null;
    }


    /**
     * 添加一条红外码
     *
     * @param devId
     * @param subId
     * @param devType
     * @param keyType
     * @param codeType
     * @param code
     * @param dbConn
     * @return
     */
    public static long addIrCode(long devId, int subId, String devType, String keyType, String keyName, String codeType, String code, Connection dbConn) {
        String sql = null;
        if (DBUtils.CODE_TYPE_API.equals(codeType)) {
            sql = "{? = call AddCodeV2(?,?,?,?,?,?,?)}";
        } else {
            sql = "{? = call AddCodeV2(?,?,?,?,?,?," + "0x" + code + ")}";
        }
        Log.e("AddCodeV2", ", devId: " + devId + ", subId: " + subId + ", devType: " + devType + ", keyType: " + keyType +
                ", keyName: " + keyName + ", codeType: " + codeType + ", code: " + code);
        CallableStatement cstmt = null;
        try {
            cstmt = (CallableStatement) dbConn.prepareCall(sql);
            cstmt.registerOutParameter(1, Types.INTEGER);
            cstmt.setLong(2, devId);
            cstmt.setInt(3, subId);
            cstmt.setString(4, devType);
            cstmt.setString(5, keyType);
            cstmt.setString(6, keyName);
            cstmt.setString(7, codeType);
            if (DBUtils.CODE_TYPE_API.equals(codeType)) {
                cstmt.setString(8, code);
            }
            cstmt.execute();
            long result = cstmt.getLong(1);
            cstmt.close();
            Log.e("bindUserDev", "addIrCode result: " + result);
            cstmt.close();
            return result;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    //    aDevID		: bigint unsigned
    //    aSubDevID	: int unsigned
    //    aKeyType	: varchar(32)
    //    aCodeType	: varchar(32)
    //    aCode		: varbinary(255)
    public static String updateCode(long devId, int subId, String keyType, String keyName, String codeType, String code, Connection dbConn) {
        String sql = "{? = call UpdateCodeV2(?,?,?,?,?," + "0x" + code + ")}";
        CallableStatement cstmt = null;
        try {
            cstmt = (CallableStatement) dbConn.prepareCall(sql);
            cstmt.registerOutParameter(1, Types.VARCHAR);
            cstmt.setLong(2, devId);
            cstmt.setInt(3, subId);
            cstmt.setString(4, keyType);
            cstmt.setString(5, keyName);
            cstmt.setString(6, codeType);
            cstmt.execute();
            String result = cstmt.getString(1);
            cstmt.close();
            Log.e("bindUserDev", "updateCode result: " + result);
            cstmt.close();
            return result;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public static String removeSubDevice(long devid, long subId) {
        Connection dbConn = getDBConn();
        if (dbConn != null) {
            String sql = "{? = call RemoveSubDevice(" + devid + "," + subId + ")}";
            CallableStatement cstmt = null;
            try {
                cstmt = (CallableStatement) dbConn.prepareCall(sql);
                cstmt.registerOutParameter(1, Types.VARCHAR);
                cstmt.execute();
                String result = cstmt.getString(1);
                cstmt.close();
                Log.e("bindUserDev", "removeSubDevice result: " + result + ", subId: " + subId + ", devid: " + devid);
                cstmt.close();
                dbConn.close();
                return result;
            } catch (SQLException e) {
                try {
                    dbConn.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
                e.printStackTrace();
            }
        }
        return null;
    }

    /**
     * 更新一个数据库中的红外码到
     * aDevID		: bigint unsigned	-	参考codemaps表的DevID字段
     * aSubDevID	: int unsigned		-	参考codemaps表的SubDevID字段
     * aCodeID		: bigint unsigned	-	参考codes表的ID字段
     * aKeyName	: varchar(32)		-	参考codemaps表的KeyName字段
     * aCodeType	: varchar(32)		-	参考codes表的Type字段
     * aCode		: blob(0)	-	参考codes表的Code字段
     */
    public static String updateCodeByID(long devid, long subId, long codeId, String keyName, String keyType, String code) {
        Connection dbConn = getDBConn();
        if (dbConn != null) {
            String sql = "{? = call UpdateCodeByID(" + devid + "," + subId + "," + codeId + ",?,?," + "0x" + code + ")}";
            CallableStatement cstmt = null;
            try {
                cstmt = (CallableStatement) dbConn.prepareCall(sql);
                cstmt.registerOutParameter(1, Types.VARCHAR);
                cstmt.setString(2, keyName);
                cstmt.setString(3, keyType);
                cstmt.execute();
                String result = cstmt.getString(1);
                cstmt.close();
                Log.e("bindUserDev", "UpdateCodeByID result: " + result + ", subId: " + subId + ", devid: " + devid);
                cstmt.close();
                dbConn.close();
                return result;
            } catch (SQLException e) {
                try {
                    dbConn.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
                e.printStackTrace();
            }
        }
        return null;
    }

    /**
     * 从数据库中删除一个指定索引ID的红外码数据
     * <p>
     * aDevID		: bigint unsigned	-	参考codemaps表的DevID字段
     * aSubDevID	: int unsigned		-	参考codemaps表的SubDevID字段
     * aCodeID		: bigint unsigned	-	参考codes表的ID字段
     */
    public static String removeCodeById(long devId, long subId, long codeId) {
        Connection dbConn = getDBConn();
        if (dbConn != null) {
            String sql = "{? = call RemoveCodeByID(" + devId + "," + subId + "," + codeId + ")}";
            CallableStatement cstmt = null;
            try {
                cstmt = (CallableStatement) dbConn.prepareCall(sql);
                cstmt.registerOutParameter(1, Types.VARCHAR);
                cstmt.execute();
                String result = cstmt.getString(1);
                cstmt.close();
                Log.e("bindUserDev", "RemoveCodeByID result: " + result + ", subId: " + subId + ", devid: " + devId);
                cstmt.close();
                dbConn.close();
                return result;
            } catch (SQLException e) {
                try {
                    dbConn.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
                e.printStackTrace();
            }
        }
        return null;
    }

    public static long addBatchItems(long batchId, ArrayList<CodeviewBean> allCodes) {
        Connection dbConn = getDBConn();
        if (dbConn != null) {
            for (CodeviewBean bean : allCodes) {
                if (bean != null) {
                    String sql = null;
                    CallableStatement cstmt = null;
                    if (DBUtils.CODE_TYPE_STATUS.equals(bean.KeyType)) {
                        sql = "{? = call AddBatchItemV2(" + batchId + "," + bean.DevID + "," + bean.SubDevID + "," + "?," + "0x" + bean.aCode + ")}";
                    } else {
                        sql = "{? = call AddBatchItem(" + batchId + "," + bean.DevID + "," + "0," + bean.CodeID + ")}";
                    }
                    Log.e("SQL", "sql: " + sql);
                    try {
                        cstmt = (CallableStatement) dbConn.prepareCall(sql);
                        cstmt.registerOutParameter(1, Types.BIGINT);
                        if (DBUtils.CODE_TYPE_STATUS.equals(bean.KeyType)) {
                            cstmt.setString(2, DBUtils.CODE_TYPE_STATUS);
                        }
                        cstmt.execute();
                        long aLong = cstmt.getLong(1);
                        cstmt.close();
                    } catch (SQLException e) {
                        if (cstmt != null) {
                            try {
                                cstmt.close();
                            } catch (SQLException e1) {
                                e1.printStackTrace();
                            }
                        }
                        e.printStackTrace();
                    }
                }
            }

            try {
                dbConn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return 0;
    }

    public static ArrayList<IrToBatchItemBean> queryBatchItems(long batchId) {
        Connection dbConn = getDBConn();
        ArrayList<IrToBatchItemBean> irCodeIds = new ArrayList<>();
        if (dbConn != null) {
            try {
                Statement statement = (Statement) dbConn.createStatement();
                ResultSet resultSet = statement.executeQuery("select DevID,Code,SubDevID from batchitems where Batch=" + batchId);
                while (resultSet.next()) {
                    IrToBatchItemBean bean = new IrToBatchItemBean(resultSet.getLong("DevID"), resultSet.getLong("Code"), resultSet.getLong("SubDevID"));
                    irCodeIds.add(bean);
                }
                statement.close();
                dbConn.close();
                return irCodeIds;
            } catch (SQLException e) {
                try {
                    dbConn.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
                e.printStackTrace();
            }
        }
        return null;
    }

    public static boolean removeBatch(long batchId) {
        Connection dbConn = getDBConn();
        if (dbConn != null) {
            String sql = "{? = call RemoveBatch(" + batchId + ")}";
            CallableStatement cstmt = null;
            try {
                cstmt = (CallableStatement) dbConn.prepareCall(sql);
                cstmt.registerOutParameter(1, Types.VARCHAR);
                cstmt.execute();
                String result = cstmt.getString(1);
                return result != null && "OK".equals(result);
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                try {
                    if (cstmt != null) {
                        cstmt.close();
                    }
                    dbConn.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
        }
        return false;
    }

    public static String getBatchIrAirCode(long codeId) {
        Connection dbConn = getDBConn();
        String codeBean = null;
        if (dbConn != null) {
            try {
                Statement statement = (Statement) dbConn.createStatement();
                ResultSet resultSet = statement.executeQuery("select HEX(Code) AS Code from codes where ID=" + codeId);
                if (resultSet.next()) {
                    codeBean = resultSet.getString("Code");
                }
                statement.close();
                dbConn.close();
                return codeBean;
            } catch (SQLException e) {
                try {
                    dbConn.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
                e.printStackTrace();
            }
        }
        return null;
    }

    public static String getBatchIrAirCode(long codeId, Connection dbConn) {
        String codeBean = null;
        if (dbConn != null) {
            try {
                Statement statement = (Statement) dbConn.createStatement();
                ResultSet resultSet = statement.executeQuery("select HEX(Code) AS Code from codes where ID=" + codeId);
                if (resultSet.next()) {
                    codeBean = resultSet.getString("Code");
                }
                statement.close();
                return codeBean;
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    public static long getKKInitId(long id){
        Connection dbConn = getDBConn();
        if (dbConn != null) {
            String sql = "{? = call GetKKDevID(?)}";
            CallableStatement cstmt = null;
            try {
                cstmt = (CallableStatement) dbConn.prepareCall(sql);
                cstmt.registerOutParameter(1, Types.BIGINT);
                cstmt.setLong(2, id);
                cstmt.execute();
                int result = cstmt.getInt(1);
                return result;
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                try {
                    if (cstmt != null) {
                        cstmt.close();
                    }
                    dbConn.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
        }
        return -1;
    }


}
